﻿//核心假设
function fillSheetCoreAssumption(context, input, result) {
    var sheet = context.workbook.worksheets.getItem("核心假设");

    //取得年报的列数
    var annualColumnCount = parseInt(result.annual.columnCount);

    //预测年份
    var forceastYear = parseInt(input.forceastYear);

    var startHistoryColumnInt = 3;
    //历史年份结束列的字母
    var endHistoryColumnChar = excelNumToString(annualColumnCount + 3);
    //预测年份列开始的字母
    var startForecastColumnInt = 3 + annualColumnCount + 1;
    var startForecastColumnChar = excelNumToString(3 + annualColumnCount + 1);
    //预测年份结束列的字母
    var endForecastColumnInt = 3 + annualColumnCount + forceastYear;
    var endForecastColumnChar = excelNumToString(3 + annualColumnCount + forceastYear);

 
    var range  = sheet.getUsedRangeOrNullObject();
    range.load('columnCount,rowCount');
    return context.sync()
        .then(function () { 
            var iCurrentColumn = range.columnCount - 3;
            var maxUsedRowCount = range.rowCount;

            //历史年份 + 预测年份
            var iMaxColumns = annualColumnCount + forceastYear;

            //当前列数不够时,则增加
            if (iCurrentColumn < iMaxColumns) {
                for (var i = 0; i < (iMaxColumns - iCurrentColumn); i++) {
                    var newRangeAdd = "D1:D{0}".format(maxUsedRowCount);
                    var newRange = sheet.getRange(newRangeAdd);
                    newRange.insert("Right");
                } 
            } else if (iCurrentColumn > iMaxColumns){
                //删除多余的列
                var clearRange = sheet.getRangeByIndexes(0, 3, maxUsedRowCount, iCurrentColumn - iMaxColumns);
                clearRange.delete("Left");
            }
        })
        .then(context.sync)
        .then(context.sync)
        .then(function () {
            //填充历史年份
            var historyValues = new Array();
            var endHistoryColumn = excelNumToString(annualColumnCount + 3);
            var address = "D7:{0}7".format(endHistoryColumn);
            var yearRange = sheet.getRange(address);
            historyValues[0] = new Array();
            for (var i = 0; i < annualColumnCount; i++) {
                var str = excelNumToString(i + 4);
                var value = "=年报数据!{0}$1".format(str); 
                historyValues[0][i] = value;
            }
            yearRange.values = historyValues; 


        }).then(context.sync)
        .then(function () { 
            //填充预测年份
            var forecastValues = new Array();
            var forecastAddress = "{0}7:{1}7".format(startForecastColumnChar, endForecastColumnChar);
            var forecastYearRange = sheet.getRange(forecastAddress);
            forecastValues[0] = new Array();
            for (var i = 0; i < forceastYear; i++) {
                var str = excelNumToString(i + startForecastColumnInt - 1);
                var value = "=LEFT({0}7,4)+1&\"E\"".format(str);
                forecastValues[0][i] = value;
            }
            forecastYearRange.values = forecastValues;
            forecastYearRange.format.font.color = "#0070C0";
            forecastYearRange.format.font.bold = true;
        })
        .then(context.sync)
        .catch(function (e) {
            console.log(e);
        });
}